<?php
include_once 'common.php';
$microtime = microtime(true);
$filter = "WHERE `year`={$payload->year} AND `status` > -1";
if ($payload->cycle != 0) {
  $filter .= " AND `cycle`={$payload->cycle}";
}
if ($payload->office != '-') {
  $filter .= " AND `office`='{$payload->office}'";
}
if (count($payload->type) > 0) {
  $filter .= " AND `type` IN (" . implode(",", $payload->type) . ")";
}
$sql = "SELECT DISTINCT `traineeName`, `traineeNumber` FROM `trex_records` {$filter}";
$traineeList = exec_sql($sql);
$sql = "SELECT DISTINCT `title` FROM `trex_records` {$filter}";
$exerciseList = exec_sql($sql);
$content = [];
$scoreAverageList = [];
$countRecordTotal = 0;
if ($traineeList) {
  foreach ($traineeList as $key => $trainee) {
    $sql = "SELECT * FROM `trex_records` {$filter} AND `traineeNumber`='{$trainee['traineeNumber']}' ORDER BY `updateTime` DESC";
    $detail = exec_sql($sql);
    if ($detail) {
      $statistic = [
        'scoreAverage' => 0,
        'scoreTotal' => 0,
        'scoreMin' => 95,
        'evaluationMin' => 5,
        'countRecord' => 0,
        'countFailedRecord' => 0,
        'countFailedSubjects' => 0,
        'summaryFailedSubjects' => 0,
      ];
      foreach ($detail as $key => $record) {
        $detail[$key]['subjects'] = json_decode($record['subjects']);
        $detail[$key]['traineeLicences'] = json_decode($record['traineeLicences']);
        $detail[$key]['subjectsComments'] = json_decode($record['subjectsComments']);
        $detail[$key]['failedSubjects'] = json_decode($record['failedSubjects']);
        $detail[$key]['evaluations'] = json_decode($record['evaluations']);
        $detail[$key]['history'] = json_decode($record['history']);
        $statistic['countRecord']++;
        $countRecordTotal++;
        $statistic['scoreTotal'] += $record['evaluationScore'];
        if ($record['evaluationScore'] < $statistic['scoreMin']) {
          $statistic['scoreMin'] = $record['evaluationScore'];
        }
        if ($record['evaluationMin'] < $statistic['evaluationMin']) {
          $statistic['evaluationMin'] = $record['evaluationMin'];
        }
        if ($record['pass'] == '否') {
          $statistic['countFailedRecord']++;
        }
        if ($record['failedSubjectsCount'] > 0) {
          $statistic['countFailedSubjects']++;
          $statistic['summaryFailedSubjects'] += $record['failedSubjectsCount'];
        }
      }
      $statistic['scoreAverage'] = round($statistic['scoreTotal'] / $statistic['countRecord'], 1);
      array_push($content, [
        'trainee' => $trainee,
        'statistic' => $statistic,
        'detail' => $detail,
      ]);
      array_push($scoreAverageList, $statistic['scoreAverage']);
    }
  }
}
$scoreAverageList = array_unique($scoreAverageList);
sort($scoreAverageList);
$microtime = round((microtime(true) - $microtime) * 1000);

$output = [
  'traineeList' => $traineeList,
  'exerciseList' => $exerciseList,
  'content' => $content,
  'scoreAverageList' => $scoreAverageList,
  'microtime' => $microtime,
  'countRecordTotal' => $countRecordTotal,
  'payload' => $payload,
  'filter' => $filter,
];
output($output);